IF EXISTS (SELECT * FROM sysobjects WHERE name = 'GetMSSQLJobScheduleDescription' AND type IN ('FN','IF','TF'))
     DROP FUNCTION dbo.GetMSSQLJobScheduleDescription
GO

CREATE FUNCTION dbo.GetMSSQLJobScheduleDescription
     ( @freq_type              INT , 
       @freq_interval          INT , 
       @freq_subday_type       INT , 
       @freq_subday_interval   INT , 
       @freq_relative_interval INT , 
       @freq_recurrence_factor INT , 
       @active_start_date      INT , 
       @active_end_date        INT, 
       @active_start_time      INT , 
       @active_end_time        INT ) 
RETURNS VARCHAR(255)
AS
/*
 *
 * GetMSSQLJobScheduleDescription
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Calling
 * Application:  
 *
 * Note(s):      
 *
 * CfgCtrl Keys: 
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 11/11/2011, 1.00.0000
 *
 */
BEGIN 
     DECLARE @schedule_description VARCHAR(255) 
     DECLARE @loop INT 

     IF (@freq_type = 0x1) -- OneTime 
     BEGIN 
          SELECT @schedule_description = 'Once on ' 
                                       + CONVERT(VARCHAR(10), @active_start_date) 
                                       + ' at ' 
                                       + CONVERT(VARCHAR(10), cast((@active_start_time / 10000) as varchar(10)) 
                                       + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2)) 
          RETURN @schedule_description 
     END 

     IF (@freq_type = 0x4) -- Daily 
     BEGIN 
          SELECT @schedule_description = 'Every day ' 
     END 

     IF (@freq_type = 0x8) -- Weekly 
     BEGIN 
          SELECT @schedule_description = 'Every ' 
                                       + CONVERT(NVARCHAR(11), @freq_recurrence_factor) 
                                       + ' week(s) on ' 

          SELECT @loop = 1 

          WHILE (@loop <= 7) 
          BEGIN 
               IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1)) 
               BEGIN
                    SELECT @schedule_description = @schedule_description 
                                                 + DATENAME(dw, '1996120' + CONVERT(NVARCHAR, @loop)) 
                                                 + ', ' 
               END
               
               SELECT @loop = @loop + 1 
          END 

          IF (RIGHT(@schedule_description, 2) = ', ') 
          BEGIN
               SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + ' ' 
          END
     END 

     IF (@freq_type = 0x10) -- Monthly 
     BEGIN 
          SELECT @schedule_description = 'Every ' 
                                       + CONVERT(VARCHAR(11), @freq_recurrence_factor) 
                                       + ' months(s) on day ' 
                                       + CONVERT(VARCHAR(11), @freq_interval) 
                                       + ' of that month ' 
     END 

     IF (@freq_type = 0x20) -- Monthly Relative 
     BEGIN 
          SELECT @schedule_description = 'Every ' 
                                       + CONVERT(VARCHAR(11), @freq_recurrence_factor) 
                                       + ' months(s) on the ' 

          SELECT @schedule_description = @schedule_description 
                                       + CASE @freq_relative_interval 
                                              WHEN 0x01 THEN 'first ' 
                                              WHEN 0x02 THEN 'second ' 
                                              WHEN 0x04 THEN 'third ' 
                                              WHEN 0x08 THEN 'fourth ' 
                                              WHEN 0x10 THEN 'last ' 
                                         END 
                                       + CASE WHEN (@freq_interval > 00) AND (@freq_interval < 08) 
                                              THEN DATENAME(dw, '1996120' + CONVERT(NVARCHAR, @freq_interval)) 
                                              WHEN (@freq_interval = 08) 
                                              THEN 'day' 
                                              WHEN (@freq_interval = 09) 
                                              THEN 'week day' 
                                              WHEN (@freq_interval = 10) 
                                              THEN 'weekend day' 
                                         END 
                                       + ' of that month ' 
     END 

     IF (@freq_type = 0x40) -- AutoStart 
     BEGIN 
          SELECT @schedule_description = FORMATMESSAGE(14579) 
          RETURN @schedule_description 
     END 

     IF (@freq_type = 0x80) -- OnIdle 
     BEGIN 
          SELECT @schedule_description = 'Starts whenever the CPU is idle.'
          RETURN @schedule_description 
     END 

     -- Subday stuff 
     SELECT @schedule_description = @schedule_description 
                                  + CASE @freq_subday_type 
                                         WHEN 0x1 THEN 'at ' 
                                                     + CONVERT(VARCHAR(10), cast((@active_start_time / 10000) as varchar(10)) 
                                                     + ':' 
                                                     + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2)) 
                                         WHEN 0x2 THEN 'every ' 
                                                     + CONVERT(VARCHAR(11), @freq_subday_interval) 
                                                     + ' second(s)' 
                                         WHEN 0x4 THEN 'every ' 
                                                     + CONVERT(VARCHAR(11), @freq_subday_interval) 
                                                     + ' minute(s)' 
                                         WHEN 0x8 THEN 'every ' 
                                                     + CONVERT(VARCHAR(11), @freq_subday_interval) 
                                                     + ' hour(s)' 
                                    END 

     IF (@freq_subday_type IN (0x2, 0x4, 0x8)) 
     BEGIN
          SELECT @schedule_description = @schedule_description 
                                       + ' between ' 
                                       + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) ) + N' and ' + CONVERT(NVARCHAR, cast((@active_end_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) ) 
     END

     RETURN @schedule_description 
END
GO
